package srx.open.com.utils;

import android.content.Context;
import android.content.res.AssetManager;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import srx.open.com.mode.AddrItem;
import srx.open.com.mode.ClassItem;

public class DataBaseHelper {

	private static final String TAG = "DataBaseHelper";
	/* 数据库表名 */
	private static final String DATA_BASE_NAME = "srx_data.db";
	/* 数据库表 */
	//  分类表
	private static final String DATA_CLASSIFY_ONE = "ClassifyOne";
	private static final String DATA_CLASSIFY_TWO = "ClassifyTwo";
	private static final String DATA_CLASSIFY_THREE = "ClassifyThree";
	// 城市表
	public static final String DATA_PROVINCE = "Province";
	public static final String DATA_CITY = "City";
	public static final String DATA_DISTRICT = "District";

	private SQLiteDatabase db;
	private Context mContext;
	private String dataBasePath = "/data/data/%s/databases";

	public DataBaseHelper(Context context) {
		mContext = context;
		db = getDatabase(DATA_BASE_NAME);
		// test database query.
		if (db != null) {
			mClassifyOneList = queryClassifyOne();
			// queryClassifyThree(800);
			// queryClassifyTwo(1500);
			// queryClassifyThree();
		}
		Log.i(TAG, "数据库db:" + db);
	}

	public List<ClassItem> mClassifyOneList;

	/**
	 * 查询类似 海南，四川省这样的.
	 */
	public List<AddrItem> queryProvince() {
		List<AddrItem> provinceList = new ArrayList<AddrItem>(); // Province列表.
		Cursor cursor = db.rawQuery("select * from " + DATA_PROVINCE, null);
		if (cursor != null) {
			while (cursor.moveToNext()) {
				AddrItem province = new AddrItem();
				province.setName(cursor.getString(cursor
						.getColumnIndex("name")));
				province.setId(cursor.getInt(cursor
						.getColumnIndex("id")));
				provinceList.add(province);
			}
		}
		return provinceList;
	}

	/**
	 * 查询类似 成都市，三亚市这样的.
	 */
	public List<AddrItem> queryCity(int provinceId) {
		List<AddrItem> provinceList = new ArrayList<AddrItem>(); // city列表.
		Cursor cursor = db.rawQuery("select * from " + DATA_CITY+ " where provinceId=?", new String[] { "" + provinceId });
		if (cursor != null) {
			while (cursor.moveToNext()) {
				AddrItem province = new AddrItem();
				province.setName(cursor.getString(cursor
						.getColumnIndex("name")));
				province.setId(cursor.getInt(cursor
						.getColumnIndex("id")));
				provinceList.add(province);
			}
		}
		return provinceList;
	}

	/**
	 * 查询类似 武侯区，高新区... ...
	 */
	public List<AddrItem> queryDistrict(int cityId) {
		List<AddrItem> provinceList = new ArrayList<AddrItem>(); // city列表.
		Cursor cursor = db.rawQuery("select * from " + DATA_DISTRICT+ " where cityId=?", new String[] { "" + cityId });
		if (cursor != null) {
			while (cursor.moveToNext()) {
				AddrItem province = new AddrItem();
				province.setName(cursor.getString(cursor
						.getColumnIndex("name")));
				province.setId(cursor.getInt(cursor
						.getColumnIndex("id")));
				provinceList.add(province);
			}
		}
		return provinceList;
	}

	/**
	 * 	根据省份信息获取id.
	 * 	比如:北京，四川，重庆 然后获取对应id.
	 */
	public AddrItem queryLocationProvinceId(String name) {
		AddrItem provinceitem = new AddrItem();
		try {
			// 去掉多余的尾巴.(只留两个字)
			if (name.length() >= 3) {
				name = name.substring(0, 2);
			}
			// 查询.
			Cursor cursor = db.rawQuery("select * from " + DATA_PROVINCE + " where name=?", new String[]{"" + name});
			if (cursor != null) {
				if (cursor.moveToNext()) {
					provinceitem.setName(cursor.getString(cursor
							.getColumnIndex("name")));
					provinceitem.setId(cursor.getInt(cursor
							.getColumnIndex("id")));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return provinceitem;
	}

	/**
	 *  根据城市信息获取id.
	 *  比如: 成都市
	 */
	public AddrItem queryLocationCityId(String name) {
		AddrItem cityItem = new AddrItem();
		Cursor cursor = db.rawQuery("select * from " + DATA_CITY +  " where name=?", new String[]{"" + name});
		if (cursor != null) {
			if (cursor.moveToNext()) {
				cityItem.setName(cursor.getString(cursor
						.getColumnIndex("name")));
				cityItem.setId(cursor.getInt(cursor
						.getColumnIndex("id")));
			}
		}
		return cityItem;
	}

	/**
	 *	根据区县信息获取id.
	 *	比如: 武侯区
	 */
	public AddrItem queryLocationDistrictId(String name) {
		AddrItem districtItem = new AddrItem();
		Cursor cursor = db.rawQuery("select * from " + DATA_DISTRICT+  " where name=?", new String[]{"" + name});
		if (cursor != null) {
			if (cursor.moveToNext()) {
				districtItem.setName(cursor.getString(cursor
						.getColumnIndex("name")));
				districtItem.setId(cursor.getInt(cursor
						.getColumnIndex("id")));
			}
		}
		return districtItem;
	}

	/**
	 *  根据ID获取Province名称.
	 */
	public String queryIdToAddrName(String tableName, int addrId) {
		String addrName = "";
		try {
			// 查询.
			Cursor cursor = db.rawQuery("select * from " + tableName + " where id=?", new String[]{"" + addrId});
			if (cursor != null) {
				if (cursor.moveToNext()) {
					addrName = cursor.getString(cursor
							.getColumnIndex("name"));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return addrName;
	}

	/*
	 * 查询分类.
	 */
	public List<ClassItem> queryClassifyOne() {
		List<ClassItem> classifyOneList = new ArrayList<ClassItem>(); // 分类一列表.
		Cursor cursor = db.rawQuery("select * from " + DATA_CLASSIFY_ONE, null);
		if (cursor != null) {
			while (cursor.moveToNext()) {
				ClassItem classifyOne = new ClassItem();
				classifyOne.setDescription(cursor.getString(cursor
						.getColumnIndex("description")));
				classifyOne.setOneId(cursor.getInt(cursor
						.getColumnIndex("oneId")));
				classifyOneList.add(classifyOne);
			}
		}
		return classifyOneList;
	}

	public String queryClassifyOne(int oneId) {
		String result = "未知";
		try {
			Cursor cursor = db.rawQuery("select * from " + DATA_CLASSIFY_ONE
					+ " where oneId=?", new String[] { "" + oneId });
			if (cursor != null) {
				if (cursor.moveToNext()) {
					result = cursor.getString(cursor
							.getColumnIndex("description"));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/*
	 * 查询分类二.
	 */
	public List<ClassItem> queryClassifyTwo(int oneId) {
		List<ClassItem> classifyTwoList = new ArrayList<ClassItem>(); // 分类二列表.
		Cursor cursor = null;
		if (oneId != -1) {
			cursor = db.rawQuery("select * from " + DATA_CLASSIFY_TWO
					+ " where oneId=?", new String[]{"" + oneId});
		} else {
			cursor = db.rawQuery("select * from " + DATA_CLASSIFY_TWO, null);
		}
		if (cursor != null) {
			while (cursor.moveToNext()) {
				ClassItem classifyTwo = new ClassItem();
				classifyTwo.setDescription(cursor.getString(cursor
						.getColumnIndex("description")));
				classifyTwo.setTwoId(cursor.getInt(cursor
						.getColumnIndex("twoId")));
				classifyTwo.setOneId(cursor.getInt(cursor
						.getColumnIndex("oneId")));
				classifyTwoList.add(classifyTwo);
			}
		}
		return classifyTwoList;
	}

	public String queryClassifyTwo2(int twoId) {
		String result = "未知";
		try {
			Cursor cursor = db.rawQuery("select * from " + DATA_CLASSIFY_TWO
					+ " where twoId=?", new String[] { "" + twoId });
			if (cursor != null) {
				if (cursor.moveToNext()) {
					result = cursor.getString(cursor
							.getColumnIndex("description"));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/*
	 * 查询分类二.
	 */
	public List<ClassItem> queryClassifyThree(int twoId) {
		List<ClassItem> classifyThreeList = new ArrayList<ClassItem>(); // 分类三列表.
		Cursor cursor = db.rawQuery("select * from " + DATA_CLASSIFY_THREE
				+ " where twoId=?", new String[] { "" + twoId });
		if (cursor != null) {
			while (cursor.moveToNext()) {
				ClassItem classifyThree = new ClassItem();
				classifyThree.setDescription(cursor.getString(cursor
						.getColumnIndex("description")));
				classifyThree.setTwoId(cursor.getInt(cursor
						.getColumnIndex("twoId")));
				classifyThree.setThreeId(cursor.getInt(cursor
						.getColumnIndex("threeId")));
				classifyThreeList.add(classifyThree);
			}
		}
		return classifyThreeList;
	}

	public String queryClassifyThree2(int threeId) {
		String result = "未知";
		try {
			Cursor cursor = db.rawQuery("select * from " + DATA_CLASSIFY_THREE
					+ " where threeId=?", new String[] { "" + threeId });
			if (cursor != null) {
				if (cursor.moveToNext()) {
					result = cursor.getString(cursor
							.getColumnIndex("description"));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	/*
	 * 获取数据库db.
	 */
	public SQLiteDatabase getDataBase() {
		return this.db;
	}

	private SQLiteDatabase getDatabase(String dbfile) {
		String spath = getDataBasePath();
		String sfile = getDataBaseFile(DATA_BASE_NAME);
		File file = new File(sfile);
		if (!file.exists()) {
			file = new File(spath);
			// 判断是否目录存在
			if (!file.exists() && !file.mkdirs()) {
				Log.i(TAG, "Create \"" + spath + "\" fail!");
				return null;
			}
			// 拷贝Assets到/data/data目录下
			if (!copyAssetsToFilesystem(dbfile, sfile)) {
				Log.i(TAG, String.format("Copy %s to %s fail!", dbfile, sfile));
				return null;
			} else {
				Log.i(TAG,
						String.format("Copy %s to %s success!", dbfile, sfile));
			}
		} else {
			Log.i(TAG, "database \"" + sfile + "\" is exists!");
		}

		SQLiteDatabase db = SQLiteDatabase.openDatabase(sfile, null,
				SQLiteDatabase.NO_LOCALIZED_COLLATORS);
		return db;
	}

	/*
	 * 复制数据.
	 */
	private boolean copyAssetsToFilesystem(String assetsSrc, String des) {
		Log.i(TAG, "Copy " + assetsSrc + " to " + des + " start");
		InputStream istream = null;
		OutputStream ostream = null;
		try {
			AssetManager am = mContext.getAssets();
			istream = am.open(assetsSrc);
			ostream = new FileOutputStream(des);
			byte[] buffer = new byte[1024];
			int length;
			while ((length = istream.read(buffer)) > 0) {
				ostream.write(buffer, 0, length);
			}
			istream.close();
			ostream.close();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				if (istream != null)
					istream.close();
				if (ostream != null)
					ostream.close();
			} catch (Exception ee) {
				ee.printStackTrace();
			}
			return false;
		}
		Log.i(TAG, "Copy " + assetsSrc + " to " + des + " end");
		return true;
	}

	/*
	 * 获取需要复制的目录.
	 */
	public String getDataBasePath() {
		String packName = this.mContext.getApplicationInfo().packageName;
		String dirPath = String.format(dataBasePath, "" + packName);
		return dirPath;
	}

	/*
	 * 获取数据库所有的路径.
	 */
	public String getDataBaseFile(String dbfile) {
		return getDataBasePath() + "/" + dbfile;
	}

	public void closeDatabase() {
		db.close();
	}

}
